import pymysql
import urllib.request
import json
from datetime import date, datetime, timedelta

db = pymysql.connect("114.115.160.16", "root", "123456Aa!", "fund")
cursor = db.cursor()

def get_price_by_id(fund_id):
    headers = {
        "token": "atTPd9c8sA"
    }
    req = urllib.request.Request(url="https://api.doctorxiong.club/v1/fund/detail?code=" + fund_id + "&startDate=2021-05-14", headers=headers)
    response = urllib.request.urlopen(req).read().decode("UTF-8")
    fund_info = json.loads(response)["data"]
    if "netWorth" not in fund_info.keys():
        return 0
    return float(fund_info["netWorth"])

sql = "select * from api_check where complete=0"
cursor.execute(sql)
for data in cursor.fetchall():
    check_id = data[0]
    user_id = data[1]
    fund_id = data[2]
    number = data[3]
    money = data[4]
    category = data[6]
    if category == 0:  # 买入
        price = get_price_by_id(fund_id)  # 当前净值

        tmp_sql = "select buy_rate from api_fund where fund_id='%s'" % (fund_id)
        cursor.execute(tmp_sql)
        tmp_tuple = cursor.fetchone()
        buy_rate = tmp_tuple[0]  # 买入费率


        tmp_sql = "select * from api_user where openid='%s'" % (user_id)
        cursor.execute(tmp_sql)
        user_money = cursor.fetchone()[4]
        if -0.01 <= user_money - money <= 0.01:
            money = user_money

        number = money * (1 - buy_rate / 100) / price  # 买入股数
        tmp_sql = "update api_check set number=%f,complete=1 where id=%d" % (number, check_id)
        cursor.execute(tmp_sql)
        db.commit()

        # hold
        tmp_sql = "select * from api_hold where user_id='%s' and fund_id='%s'" % (user_id, fund_id)
        cursor.execute(tmp_sql)
        holds = cursor.fetchall()
        cursor.execute("select max(id) from api_hold")
        max_id = cursor.fetchall()[0][0]
        if max_id == None:
            max_id = 1
        else:
            max_id += 1
        if len(holds) == 0:
            tmp_sql = "insert into api_hold values(%d,'%s','%s',%f,%f)" % (max_id, user_id, fund_id, money, number)
            cursor.execute(tmp_sql)
            db.commit()
        else:
            haved_money = holds[0][3]
            haved_number = holds[0][4]
            tmp_sql = "update api_hold set cost=%f,number=%f where user_id='%s' and fund_id='%s'" % (
                haved_money + money, haved_number + number, user_id, fund_id)
            cursor.execute(tmp_sql)
            db.commit()
    else:   # 卖出
        price = get_price_by_id(fund_id)  # 当前净值

        # check
        money = number * price
        tmp_sql = "update api_check set complete=1,money=%f where id='%s'" % (money, check_id)
        cursor.execute(tmp_sql)
        db.commit()

        # user
        tmp_sql = "select * from api_user where openid='%s'" % (user_id)
        cursor.execute(tmp_sql)
        user_money = cursor.fetchone()[4]
        tmp_sql = "update api_user set money=%f where openid='%s'" % (user_money + money, user_id)
        cursor.execute(tmp_sql)
        db.commit()
'''
sql = "select * from api_queue"
cursor.execute(sql)
for data in cursor.fetchall():
    if data[3] == 0:  # 买入
        continue
        user_id = data[1]
        fund_id = data[2]
        money = data[4]
        time = data[6]

        tmp_sql = "select * from api_vary where fund_id='%s'" % (fund_id)
        cursor.execute(tmp_sql)
        tmp_tuple = cursor.fetchall()
        price = tmp_tuple[len(tmp_tuple) - 1][2]  # 当前净值

        tmp_sql = "select buy_min from api_fund where fund_id='%s'" % (fund_id)
        cursor.execute(tmp_sql)
        tmp_tuple = cursor.fetchone()
        buymin = tmp_tuple[0]  # 买入费率

        number = money * (1 - buymin) / price

        # check
        cursor.execute("select max(id) from api_check")
        max_id = cursor.fetchall()[0][0]
        if max_id == None:
            max_id = 1
        else:
            max_id += 1
        tmp_sql = "insert into api_check values(%d,'%s','%s',%f,%f,'%s',0,1)" % (
        max_id, user_id, fund_id, number, money, str(time))
        cursor.execute(tmp_sql)
        db.commit()

        # hold
        tmp_sql = "select * from api_hold where user_id='%s' and fund_id='%s'" % (user_id, fund_id)
        cursor.execute(tmp_sql)
        holds = cursor.fetchall()
        cursor.execute("select max(id) from api_hold")
        max_id = cursor.fetchall()[0][0]
        if max_id == None:
            max_id = 1
        else:
            max_id += 1
        if len(holds) == 0:
            tmp_sql = "insert into api_hold values(%d,'%s','%s',%f,%f)" % (max_id, user_id, fund_id, money, number)
            cursor.execute(tmp_sql)
            db.commit()
        else:
            haved_money = holds[0][3]
            haved_number = holds[0][4]
            tmp_sql = "update api_hold set cost=%f,number=%f where user_id='%s' and fund_id='%s'" % (
            haved_money + money, haved_number + number, user_id, fund_id)
            cursor.execute(tmp_sql)
            db.commit()

    else:  # 卖出
        continue
        user_id = data[1]
        fund_id = data[2]
        number = data[5]
        time = data[6]
        tmp_sql = "select * from api_vary where fund_id='%s'" % (fund_id)
        cursor.execute(tmp_sql)
        tmp_tuple = cursor.fetchall()
        price = tmp_tuple[len(tmp_tuple) - 1][2]  # 当前净值


        tmp_sql = "select * from api_hold where user_id='%s' and fund_id='%s'" % (user_id,fund_id)
        cursor.execute(tmp_sql)
        user_number = cursor.fetchone()[4]
        if -0.01 <= user_number - number <= 0.01:
            number = user_number        # 全卖出


        money = number * price

        # check
        cursor.execute("select max(id) from api_check")
        max_id = cursor.fetchall()[0][0]
        if max_id == None:
            max_id = 1
        else:
            max_id += 1

        tmp_sql = "insert into api_check values(%d,'%s','%s',%f,%f,'%s',1,1)" % (
        max_id, user_id, fund_id, number, money, str(time))
        cursor.execute(tmp_sql)
        db.commit()

        # user
        tmp_sql = "select * from api_user where openid='%s'" % (user_id)
        cursor.execute(tmp_sql)
        user = cursor.fetchone()
        haved_money = user[4]
        tmp_sql = "update api_user set money=%f where openid='%s'" % (haved_money + money, user_id)
        cursor.execute(tmp_sql)
        db.commit()


        # hold
        if user_number == number:   # 删除记录
            tmp_sql = "delete from api_hold where user_id='%s' and fund_id='%s'" % (user_id, fund_id)
            cursor.execute(tmp_sql)
            db.commit()
        else:
            tmp_sql = "update api_hold set number=%f where user_id='%s' and fund_id='%s'" % (user_number - number, user_id, fund_id)
            cursor.execute(tmp_sql)
            db.commit()

'''
sql = "delete from api_queue"
cursor.execute(sql)
db.commit()
